﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class DeliveryOrderDetailDA : IDeliveryOrderDetailDA
    {
        private string ACCEPT_QTY = "@ACCEPT_QTY";
        private string BREAKAGE_QTY = "@BREAKAGE_QTY";
        private string CREATED_BY = "@CREATED_BY";
        private string DELIVERY_ORDER_ID = "@DELIVERY_ORDER_ID";
        private string EXTERN_ORDER_ID = "@EXTERN_ORDER_ID";
        private string EXTERN_ORDER_LINE_ID = "@EXTERN_ORDER_LINE_ID";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LINE_ID = "@LINE_ID";
        private string LOADING_QTY = "@LOADING_QTY";
        private string LOSE_QTY = "@LOSE_QTY";
        private string ORIGINAL_QTY = "@ORIGINAL_QTY";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PKG_DEMAGED_QTY = "@PKG_DEMAGED_QTY";
        private string REJECTED_QTY = "@REJECTED_QTY";
        private string REMARK = "@REMARK";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_DELIVERY_ORDER_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@DELIVERY_ORDER_DETAIL WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_DELIVERY_ORDER_DETAIL = " DELETE FROM @_@DELIVERY_ORDER_DETAIL WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID  ";
        private string SQL_INSERT_DELIVERY_ORDER_DETAIL = " INSERT INTO @_@DELIVERY_ORDER_DETAIL ( WH_ID, DELIVERY_ORDER_ID, LINE_ID, EXTERN_ORDER_LINE_ID, OWNER_ID, SKU_ID, STATUS, PACK_ID, UOM_ID, ORIGINAL_QTY, LOADING_QTY, ACCEPT_QTY, LOSE_QTY, BREAKAGE_QTY, PKG_DEMAGED_QTY, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,EXTERN_ORDER_ID) VALUES ( @WH_ID, @DELIVERY_ORDER_ID, @LINE_ID, @EXTERN_ORDER_LINE_ID, @OWNER_ID, @SKU_ID, @STATUS, @PACK_ID, @UOM_ID, @ORIGINAL_QTY, @LOADING_QTY, @ACCEPT_QTY, @LOSE_QTY, @BREAKAGE_QTY, @PKG_DEMAGED_QTY, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@EXTERN_ORDER_ID )  ";
        private string SQL_SELECT_ALL_DELIVERY_ORDER_DETAIL = "SELECT  DOD.ROWID, DOD.WH_ID, DOD.DELIVERY_ORDER_ID, DOD.LINE_ID, DOD.EXTERN_ORDER_LINE_ID, DOD.OWNER_ID, DOD.SKU_ID,  DOD.STATUS AS STATUS,  T.[TEXT] AS DOD_STATUS,   DOD.PACK_ID, DOD.UOM_ID, DOD.ORIGINAL_QTY, DOD.LOADING_QTY, DOD.ACCEPT_QTY, DOD.REJECTED_QTY, DOD.LOSE_QTY, DOD.BREAKAGE_QTY, DOD.PKG_DEMAGED_QTY, DOD.UDF1, DOD.UDF2, DOD.UDF3, DOD.UDF4, DOD.UDF5, DOD.UDF6, DOD.UDF7, DOD.UDF8, DOD.UDF9, DOD.UDF10, DOD.REMARK, DOD.IS_ACTIVE, DOD.TS, DOD.CREATED_BY, DOD.CREATED_DATE, DOD.UPDATED_BY, DOD.UPDATED_DATE ,DOD.EXTERN_ORDER_ID   FROM  @_@DELIVERY_ORDER_DETAIL as DOD  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = DOD.STATUS AND SCD.SYS_CODE_TYPE = 'DeliveryOrderStatus'  LEFT JOIN sysadm.TRANSLATION T ON 'DeliveryOrderStatus.'+DOD.STATUS = T.ITEM_ID       AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE  INNER JOIN SYSADM.USER_OWNER UO ON DOD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_DELIVERY_ORDERANDORDERDETAIL_BYIDS = "SELECT     dOrder.DELIVERY_ORDER_ID, dOrder.EXTERN_ORDER_ID, dOrder.OWNER_ID, dOrder.CUSTOMER_ID, dOrder.EXPECT_ARRIVAL_DATE,   dOrder.TRANSFER_TYPE, dOrder.DELIVERY_ORDER_TYPE, dOrder.TOTAL_QTY, dOrder.TOTAL_WEIGHT, dOrder.TOTAL_CUBE, dOrderDe.SKU_ID,   dOrderDe.PACK_ID, dOrderDe.UOM_ID, dOrderDe.LOADING_QTY, dOrder.START_LOC_ADDR,   dOrderDe.ORIGINAL_QTY - dOrderDe.LOADING_QTY AS ResQty, dOrderDe.ORIGINAL_QTY,dOrder.REQUESTED_SHIP_DATE,dOrderDe.LINE_ID  FROM         @_@DELIVERY_ORDER AS dOrder INNER JOIN  @_@DELIVERY_ORDER_DETAIL AS dOrderDe ON dOrder.DELIVERY_ORDER_ID = dOrderDe.DELIVERY_ORDER_ID ";
        private string SQL_UPDATE_DELIVERY_ORDER_DETAIL = " UPDATE @_@DELIVERY_ORDER_DETAIL SET WH_ID = @WH_ID, EXTERN_ORDER_LINE_ID = @EXTERN_ORDER_LINE_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, STATUS = @STATUS, PACK_ID = @PACK_ID, UOM_ID = @UOM_ID, ORIGINAL_QTY = @ORIGINAL_QTY, LOADING_QTY = @LOADING_QTY, ACCEPT_QTY = @ACCEPT_QTY, LOSE_QTY = @LOSE_QTY, BREAKAGE_QTY = @BREAKAGE_QTY, PKG_DEMAGED_QTY = @PKG_DEMAGED_QTY, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),EXTERN_ORDER_ID=@EXTERN_ORDER_ID WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID AND LINE_ID = @LINE_ID  ";
        private string STATUS = "@STATUS";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UOM_ID = "@UOM_ID";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckDeliveryOrderDetailIDUnique(string deliveryOrderID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID), new SqlParameter("@LINE_ID", lineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_DELIVERY_ORDER_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckDeliveryOrderDetailIDUnique(DataBase dataBase, DbTransaction tran, string deliveryOrderID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID), new SqlParameter("@LINE_ID", lineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_DELIVERY_ORDER_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteDeliveryOrderDetail(List<string> deliveryOrderDetailID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < deliveryOrderDetailID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderDetailID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteDeliveryOrderDetail(List<string> deliveryOrderID, List<string> lineID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    if (deliveryOrderID.Count != lineID.Count)
                    {
                        throw new Exception("deliveryOrderID和lineID数量不对应!");
                    }
                    for (int j = 0; j < deliveryOrderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID[j]), new SqlParameter("@LINE_ID", lineID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID[i]) + " AND LINE_ID = @LINE_ID", paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteDeliveryOrderDetail(DataBase dataBase, DbTransaction tran, List<string> deliveryOrderDetailID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < deliveryOrderDetailID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderDetailID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteDeliveryOrderDetail(DataBase dataBase, DbTransaction tran, List<string> deliveryOrderID, List<string> lineID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    if (deliveryOrderID.Count != lineID.Count)
                    {
                        throw new Exception("deliveryOrderID和lineID数量不对应!");
                    }
                    for (int j = 0; j < deliveryOrderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID[j]), new SqlParameter("@LINE_ID", lineID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID[i]) + " AND LINE_ID = @LINE_ID", paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllDeliveryOrderDetail(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllDeliveryOrderDetail(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<DeliveryOrderAndDispatchDataInfo> GetDeliveryOrderAndOrderDetailByIDs(List<string> deliveryOrderIDs, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_DELIVERY_ORDERANDORDERDETAIL_BYIDS;
            if ((deliveryOrderIDs != null) && (deliveryOrderIDs.Count > 0))
            {
                temp = temp + " where dOrderDe.DELIVERY_ORDER_ID in ( ";
                for (int i = 0; i < deliveryOrderIDs.Count; i++)
                {
                    temp = temp + "'" + deliveryOrderIDs[i].ToString() + "',";
                }
            }
            if (temp.EndsWith(","))
            {
                temp = temp.Substring(0, temp.Length - 1);
            }
            temp = (temp + ")").Replace("@_@", whLoginID);
            List<DeliveryOrderAndDispatchDataInfo> deliveryOrderAndDispatchDataInfos = new List<DeliveryOrderAndDispatchDataInfo>();
            DeliveryOrderAndDispatchDataInfo deliveryOrderAndDispatchDataInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, temp))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    deliveryOrderAndDispatchDataInfo = new DeliveryOrderAndDispatchDataInfo(reader["DELIVERY_ORDER_ID"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["CUSTOMER_ID"].ToString(), (reader["EXPECT_ARRIVAL_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECT_ARRIVAL_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TRANSFER_TYPE"].ToString(), reader["DELIVERY_ORDER_TYPE"].ToString(), (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, (reader["TOTAL_WEIGHT"].ToString() != "") ? decimal.Parse(reader["TOTAL_WEIGHT"].ToString()) : 0M, (reader["TOTAL_CUBE"].ToString() != "") ? decimal.Parse(reader["TOTAL_CUBE"].ToString()) : 0M, reader["SKU_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["LOADING_QTY"].ToString() != "") ? decimal.Parse(reader["LOADING_QTY"].ToString()) : 0M, (reader["ResQty"].ToString() != "") ? decimal.Parse(reader["ResQty"].ToString()) : 0M, reader["START_LOC_ADDR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), reader["LINE_ID"].ToString());
                    deliveryOrderAndDispatchDataInfos.Add(deliveryOrderAndDispatchDataInfo);
                }
                if (!reader.IsClosed)
                {
                    reader.Close();
                }
            }
            return deliveryOrderAndDispatchDataInfos;
        }

        public List<DeliveryOrderAndDispatchDataInfo> GetDeliveryOrderAndOrderDetailByIDs(DataBase dataBase, DbTransaction tran, List<string> deliveryOrderIDs, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_DELIVERY_ORDERANDORDERDETAIL_BYIDS;
            if ((deliveryOrderIDs != null) && (deliveryOrderIDs.Count > 0))
            {
                temp = temp + " where dOrderDe.DELIVERY_ORDER_ID in ( ";
                for (int i = 0; i < deliveryOrderIDs.Count; i++)
                {
                    temp = temp + "'" + deliveryOrderIDs[i].ToString() + "',";
                }
            }
            if (temp.EndsWith(","))
            {
                temp = temp.Substring(0, temp.Length - 1);
            }
            temp = (temp + ")").Replace("@_@", whLoginID);
            List<DeliveryOrderAndDispatchDataInfo> deliveryOrderAndDispatchDataInfos = new List<DeliveryOrderAndDispatchDataInfo>();
            DeliveryOrderAndDispatchDataInfo deliveryOrderAndDispatchDataInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, temp);
            while (reader.Read())
            {
                DateTime CS00002;
                deliveryOrderAndDispatchDataInfo = new DeliveryOrderAndDispatchDataInfo(reader["DELIVERY_ORDER_ID"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["CUSTOMER_ID"].ToString(), (reader["EXPECT_ARRIVAL_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECT_ARRIVAL_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TRANSFER_TYPE"].ToString(), reader["DELIVERY_ORDER_TYPE"].ToString(), (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, (reader["TOTAL_WEIGHT"].ToString() != "") ? decimal.Parse(reader["TOTAL_WEIGHT"].ToString()) : 0M, (reader["TOTAL_CUBE"].ToString() != "") ? decimal.Parse(reader["TOTAL_CUBE"].ToString()) : 0M, reader["SKU_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["LOADING_QTY"].ToString() != "") ? decimal.Parse(reader["LOADING_QTY"].ToString()) : 0M, (reader["ResQty"].ToString() != "") ? decimal.Parse(reader["ResQty"].ToString()) : 0M, reader["START_LOC_ADDR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), reader["LINE_ID"].ToString());
                deliveryOrderAndDispatchDataInfos.Add(deliveryOrderAndDispatchDataInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return deliveryOrderAndDispatchDataInfos;
        }

        public DeliveryOrderDetailInfo GetDeliveryOrderDetailByID(string deliveryOrderID, string lineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID) + " WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID), new SqlParameter("@LINE_ID", lineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            DeliveryOrderDetailInfo deliveryOrderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    deliveryOrderDetailInfo = new DeliveryOrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DELIVERY_ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["STATUS"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["LOADING_QTY"].ToString() != "") ? decimal.Parse(reader["LOADING_QTY"].ToString()) : 0M, (reader["ACCEPT_QTY"].ToString() != "") ? decimal.Parse(reader["ACCEPT_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, (reader["LOSE_QTY"].ToString() != "") ? decimal.Parse(reader["LOSE_QTY"].ToString()) : 0M, (reader["BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["BREAKAGE_QTY"].ToString()) : 0M, (reader["PKG_DEMAGED_QTY"].ToString() != "") ? decimal.Parse(reader["PKG_DEMAGED_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["DOD_Status"].ToString(), reader["EXTERN_ORDER_ID"].ToString());
                }
                if (!reader.IsClosed)
                {
                    reader.Close();
                }
            }
            return deliveryOrderDetailInfo;
        }

        public DeliveryOrderDetailInfo GetDeliveryOrderDetailByID(DataBase dataBase, DbTransaction tran, string deliveryOrderID, string lineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID) + " WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID), new SqlParameter("@LINE_ID", lineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            DeliveryOrderDetailInfo deliveryOrderDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                deliveryOrderDetailInfo = new DeliveryOrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DELIVERY_ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["STATUS"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["LOADING_QTY"].ToString() != "") ? decimal.Parse(reader["LOADING_QTY"].ToString()) : 0M, (reader["ACCEPT_QTY"].ToString() != "") ? decimal.Parse(reader["ACCEPT_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, (reader["LOSE_QTY"].ToString() != "") ? decimal.Parse(reader["LOSE_QTY"].ToString()) : 0M, (reader["BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["BREAKAGE_QTY"].ToString()) : 0M, (reader["PKG_DEMAGED_QTY"].ToString() != "") ? decimal.Parse(reader["PKG_DEMAGED_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["DOD_Status"].ToString(), reader["EXTERN_ORDER_ID"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return deliveryOrderDetailInfo;
        }

        public DataSet GetDeliveryOrderDetailByQueryList(List<string> sqlWhere, DeliveryOrderDetailQueryEntity deliveryorderdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!deliveryorderdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, deliveryorderdetailQuery.CurrentPage, deliveryorderdetailQuery.PageSize, deliveryorderdetailQuery.SortField, deliveryorderdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetDeliveryOrderDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, DeliveryOrderDetailQueryEntity deliveryorderdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (deliveryorderdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, deliveryorderdetailQuery.CurrentPage, deliveryorderdetailQuery.PageSize, deliveryorderdetailQuery.SortField, deliveryorderdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<DeliveryOrderDetailInfo> GetDeliveryOrderDetailListByID(string deliveryOrderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID) + " WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID ORDER BY DOD.ROWID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.DELIVERY_ORDER_ID, deliveryOrderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<DeliveryOrderDetailInfo> deliveryOrderDetailInfos = new List<DeliveryOrderDetailInfo>();
            DeliveryOrderDetailInfo deliveryOrderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    deliveryOrderDetailInfo = new DeliveryOrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DELIVERY_ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["STATUS"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["LOADING_QTY"].ToString() != "") ? decimal.Parse(reader["LOADING_QTY"].ToString()) : 0M, (reader["ACCEPT_QTY"].ToString() != "") ? decimal.Parse(reader["ACCEPT_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, (reader["LOSE_QTY"].ToString() != "") ? decimal.Parse(reader["LOSE_QTY"].ToString()) : 0M, (reader["BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["BREAKAGE_QTY"].ToString()) : 0M, (reader["PKG_DEMAGED_QTY"].ToString() != "") ? decimal.Parse(reader["PKG_DEMAGED_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["DOD_Status"].ToString(), reader["EXTERN_ORDER_ID"].ToString());
                    deliveryOrderDetailInfos.Add(deliveryOrderDetailInfo);
                }
            }
            return deliveryOrderDetailInfos;
        }

        public List<DeliveryOrderDetailInfo> GetDeliveryOrderDetailListByID(DataBase dataBase, DbTransaction tran, string deliveryOrderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID) + " WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID ORDER BY DOD.ROWID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.DELIVERY_ORDER_ID, deliveryOrderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<DeliveryOrderDetailInfo> deliveryOrderDetailInfos = new List<DeliveryOrderDetailInfo>();
            DeliveryOrderDetailInfo deliveryOrderDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                deliveryOrderDetailInfo = new DeliveryOrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DELIVERY_ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["STATUS"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["LOADING_QTY"].ToString() != "") ? decimal.Parse(reader["LOADING_QTY"].ToString()) : 0M, (reader["ACCEPT_QTY"].ToString() != "") ? decimal.Parse(reader["ACCEPT_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, (reader["LOSE_QTY"].ToString() != "") ? decimal.Parse(reader["LOSE_QTY"].ToString()) : 0M, (reader["BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["BREAKAGE_QTY"].ToString()) : 0M, (reader["PKG_DEMAGED_QTY"].ToString() != "") ? decimal.Parse(reader["PKG_DEMAGED_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["DOD_Status"].ToString(), reader["EXTERN_ORDER_ID"].ToString());
                deliveryOrderDetailInfos.Add(deliveryOrderDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return deliveryOrderDetailInfos;
        }

        public int InsertDeliveryOrderDetail(DeliveryOrderDetailInfo deliveryOrderDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    deliveryOrderDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DeliveryOrderDetail_Parameters(deliveryOrderDetailInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertDeliveryOrderDetail(DataBase dataBase, DbTransaction tran, DeliveryOrderDetailInfo deliveryOrderDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    deliveryOrderDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DeliveryOrderDetail_Parameters(deliveryOrderDetailInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_DeliveryOrderDetail_Parameters(DeliveryOrderDetailInfo deliveryOrderDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DELIVERY_ORDER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.EXTERN_ORDER_LINE_ID, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 30), new SqlParameter(this.STATUS, SqlDbType.VarChar, 30), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 30), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 30), new SqlParameter(this.ORIGINAL_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.LOADING_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ACCEPT_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.REJECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.LOSE_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.BREAKAGE_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PKG_DEMAGED_QTY, SqlDbType.Decimal, 13), 
                new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 100), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 30), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 30), new SqlParameter(this.EXTERN_ORDER_ID, SqlDbType.VarChar, 30)
             };
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.WhID))
            {
                paramArray[0].Value = deliveryOrderDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = deliveryOrderDetailInfo.DeliveryOrderID;
            paramArray[2].Value = deliveryOrderDetailInfo.LineID;
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.ExternOrderLineID))
            {
                paramArray[3].Value = deliveryOrderDetailInfo.ExternOrderLineID;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            paramArray[4].Value = deliveryOrderDetailInfo.OwnerID;
            paramArray[5].Value = deliveryOrderDetailInfo.SkuID;
            paramArray[6].Value = deliveryOrderDetailInfo.Status;
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.PackID))
            {
                paramArray[7].Value = deliveryOrderDetailInfo.PackID;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.UomID))
            {
                paramArray[8].Value = deliveryOrderDetailInfo.UomID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = deliveryOrderDetailInfo.OriginalQty;
            paramArray[10].Value = deliveryOrderDetailInfo.LoadingQty;
            paramArray[11].Value = deliveryOrderDetailInfo.AcceptQty;
            paramArray[12].Value = deliveryOrderDetailInfo.RejectedQty;
            paramArray[13].Value = deliveryOrderDetailInfo.LoseQty;
            paramArray[14].Value = deliveryOrderDetailInfo.BreakageQty;
            paramArray[15].Value = deliveryOrderDetailInfo.PkgDemagedQty;
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf1))
            {
                paramArray[0x10].Value = deliveryOrderDetailInfo.Udf1;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf2))
            {
                paramArray[0x11].Value = deliveryOrderDetailInfo.Udf2;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf3))
            {
                paramArray[0x12].Value = deliveryOrderDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf4))
            {
                paramArray[0x13].Value = deliveryOrderDetailInfo.Udf4;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf5))
            {
                paramArray[20].Value = deliveryOrderDetailInfo.Udf5;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf6))
            {
                paramArray[0x15].Value = deliveryOrderDetailInfo.Udf6;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf7))
            {
                paramArray[0x16].Value = deliveryOrderDetailInfo.Udf7;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf8))
            {
                paramArray[0x17].Value = deliveryOrderDetailInfo.Udf8;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf9))
            {
                paramArray[0x18].Value = deliveryOrderDetailInfo.Udf9;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Udf10))
            {
                paramArray[0x19].Value = deliveryOrderDetailInfo.Udf10;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.Remark))
            {
                paramArray[0x1a].Value = deliveryOrderDetailInfo.Remark;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            paramArray[0x1b].Value = deliveryOrderDetailInfo.IsActive;
            paramArray[0x1c].Value = deliveryOrderDetailInfo.CreatedBy;
            paramArray[0x1d].Value = deliveryOrderDetailInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(deliveryOrderDetailInfo.ExternOrderID))
            {
                paramArray[30].Value = deliveryOrderDetailInfo.ExternOrderID;
                return paramArray;
            }
            paramArray[30].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateDeliveryOrderDetail(DeliveryOrderDetailInfo deliveryOrderDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    deliveryOrderDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DeliveryOrderDetail_Parameters(deliveryOrderDetailInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateDeliveryOrderDetail(DataBase dataBase, DbTransaction tran, DeliveryOrderDetailInfo deliveryOrderDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    deliveryOrderDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DeliveryOrderDetail_Parameters(deliveryOrderDetailInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DELIVERY_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateUpdateDeliveryOrderDetailPOD(DataBase dataBase, DbTransaction tran, List<DeliveryOrderDetailInfo> deliveryOrderDetailList, string updatedby, string whLoginID)
        {
            int result = 0;
            try
            {
                foreach (DeliveryOrderDetailInfo info in deliveryOrderDetailList)
                {
                    string sql = "UPDATE @_@DELIVERY_ORDER_DETAIL SET STATUS=@STATUS,ACCEPT_QTY=@ACCEPT_QTY,REJECTED_QTY=@REJECTED_QTY,LOSE_QTY=@LOSE_QTY,BREAKAGE_QTY=@BREAKAGE_QTY,PKG_DEMAGED_QTY=@PKG_DEMAGED_QTY,UPDATED_BY=@UPDATED_BY WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID AND LINE_ID = @LINE_ID  ";
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", info.DeliveryOrderID), new SqlParameter("@LINE_ID", info.LineID), new SqlParameter("@STATUS", "Signoff"), new SqlParameter("@ACCEPT_QTY", info.AcceptQty), new SqlParameter("@REJECTED_QTY", info.RejectedQty), new SqlParameter("@LOSE_QTY", info.LoseQty), new SqlParameter("@BREAKAGE_QTY", info.BreakageQty), new SqlParameter("@PKG_DEMAGED_QTY", info.PkgDemagedQty), new SqlParameter("@UPDATED_BY", updatedby) };
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

